
<html><HEAD>
<LINK REL=STYLESHEET HREF="default.css" TYPE="text/css">
<TITLE>
SQL Server 2008 features</TITLE>
</HEAD>
<BODY>

<!-- Header -->
<p class="ancestor" align="right"><A HREF="connpbp60.htm">Previous</A>&nbsp;&nbsp;<A HREF="connpbp62.htm" >Next</A>
<!-- End Header -->
<A NAME="BCGIAJGG"></A><h1>SQL Server 2008 features</h1>
<A NAME="TI1242"></A><p>PowerBuilder support for connections to SQL Server 2008 databases
includes new database parameters as well as support for new SQL
Server datatypes. To connect to SQL Server 2008 from PowerBuilder,
you must install the SNC 10.0 driver. </p>
<A NAME="TI1243"></A><h2>New database parameters</h2>
<A NAME="TI1244"></A><h4>Provider parameter</h4>
<A NAME="TI1245"></A><p>The Provider DBParm parameter for the SQL Native Client (SNC)
interface allows you to select the SQL Server version that you want
to connect to. You can set this parameter in script to SQLNCLI (for
the SNC 9.0 driver that connect to SQL Server 2005) or to SQLNCLI10
(for the SNC 10.0 driver that connects to SQL Server 2008). Otherwise,
you can select one of these providers on the Connection tab of the
Database Profile Setup dialog box for the SNC interface. </p>
<A NAME="TI1246"></A><p>If you do not set or select a provider, the default selection
is SQLNCLI (SNC 9.0 for SQL Server 2005). This allows existing SNC
interface users to be able to migrate to PowerBuilder 11.5 without
any modifications. If PowerBuilder fails to connect with the SQLNCLI
provider, it will attempt to connect to SQLNCLI10 provider. However,
if you explicitly set the provider and the connection fails, PowerBuilder
displays an error message.</p>
<A NAME="TI1247"></A><h4>Failover parameter</h4>
<A NAME="TI1248"></A><p>The FailoverPartner DBParm parameter allows you to set the
name of a mirror server, thereby maintaining database availability
if a failover event occurs. You can also set the name of the mirror
server on the System tab of the Database Profile Setup dialog box
for the SNC interface.</p>
<A NAME="TI1249"></A><p>When failover occurs, the existing PowerBuilder connection
to SQL Server is lost. The SNC driver releases the existing connection
and tries to reopen it.  If reconnection succeeds, PowerBuilder
triggers the failover event.The following conditions must be satisfied
for PowerBuilder to trigger the failover event:    <A NAME="TI1250"></A>
<ul>
<li class=fi>The FailoverPartner DBParm is supplied at connect
time</li>
<li class=ds>The SQL Server database is configured for mirroring</li>
<li class=ds>    PowerBuilder is able to reconnect successfully
when the existing connection is lost
</li>
</ul>
</p>
<A NAME="TI1251"></A><p>When failover occurs: <A NAME="TI1252"></A>
<ul>
<li class=fi>PowerBuilder returns
an error code (998) and triggers the failover event</li>
<li class=ds>Existing cursors cannot be used and should be closed</li>
<li class=ds>Any failed database operation can be tried again</li>
<li class=ds>Any uncommitted transaction is lost. New transactions
must be started
</li>
</ul>
</p>
<A NAME="BCGJBJHD"></A><h2>Support for new datatypes in SQL Server 2008</h2>
<A NAME="TI1253"></A><h4>Date and time datatypes</h4>
<A NAME="TI1254"></A><p>The following table lists new SQL Server 2008 date and time
datatypes and the PowerScript datatypes that they map to:</p>
<A NAME="TI1255"></A><table cellspacing=0 cellpadding=6 border=1 frame="void" rules="all"><tr><th  rowspan="1"  ><A NAME="TI1256"></A>SQL Server datatype</th>
<th  rowspan="1"  ><A NAME="TI1257"></A>PowerScript datatype</th>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1258"></A>DATE</td>
<td  rowspan="1"  ><A NAME="TI1259"></A>Date</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1260"></A>TIME</td>
<td  rowspan="1"  ><A NAME="TI1261"></A>Time (Supports only up to 6 fractional
seconds precision although SQL Server datatype supports up to 7
fractional seconds precision.)</td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI1262"></A>DATETIME2</td>
<td  rowspan="1"  ><A NAME="TI1263"></A>DateTime (Supports only up to 6 fractional
seconds precision although SQL Server datatype supports up to 7
fractional seconds precision.)</td>
</tr>
</table>
<A NAME="TI1264"></A><p>The SQL Server 2008 DATETIMEOFFSET datatype is not supported
in PowerBuilder 11.5.</p>
<p><b>Precision settings</b>   When you map to a table column in a SQL Server 2008 database,
PowerBuilder includes a column labeled "Dec" in
the Column Specifications view of the DataWindow painterDataWindow painter,
and a text box labeled "Fractional Seconds Precision" in
the Column (Object Details) view of the Database painter. These
fields allow you to list the precision that you want for the TIME
and DATETIME2 columns.. </p>
<A NAME="TI1265"></A><p>The precision setting is for table creation only. When retrieving
or updating the data in a column, PowerBuilder uses only up to six
decimal places precision for fractional seconds, even if you enter
a higher precision value for the column.</p>
<A NAME="TI1266"></A><h4>Filestream datatype</h4>
<A NAME="TI1267"></A><p>The FILESTREAM datatype allows large binary data to be stored
directly in an NTFS file system. Transact-SQL statements can insert,
update, query, search, and back up FILESTREAM data. </p>
<A NAME="TI1268"></A><p>The SQL Server Database Engine implements FILESTREAM as a Varbinary(max)
datatype. The PowerBuilder SNC interface maps the Varbinary(max)
datatype to a BLOB datatype, so to retrieve or update filestream
data, use the SelectBlob or UpdateBlob SQL statements, respectively.
To specify that a column should store data on the file system, you must
include the FILESTREAM attribute in the Varbinary(max) column definition.
For example:<p><PRE> CREATE TABLE FSTest (</PRE><PRE>   GuidCol1 uniqueidentifier ROWGUIDCOL NOT NULL </PRE><PRE>   UNIQUE DEFAULT NEWID(), </PRE><PRE>   IntCol2 int, </PRE><PRE>   varbinaryCol3 varbinary(max) FILESTREAM);</PRE></p>
<p><img src="images/note.gif" width=17 height=17 border=0 align="bottom" alt="Note"> <span class=shaded>Do not use PowerScript file access functions with FILESTREAM
data</span> <A NAME="TI1269"></A>You can access FILESTREAM data by declaring and using the
Win32 API functions directly in PowerBuilder applications. However,
existing PowerBuilder file access functions cannot be used to access
FILESTREAM files. For more information about accessing FILESTREAM
data using Win32 APIs, see the <A HREF="http://msdn.microsoft.com/en-us/library/bb933877(SQL.100).aspx">MSDN SQL Server Developer Center Web site</A>
.</p>
<A NAME="TI1270"></A><h4>Using CLR datatypes in PowerBuilder</h4>
<A NAME="TI1271"></A><p>The binary values of the .NET Common Language Runtime (CLR)
datatypes can be retrieved from a SQL Server database as blobs that
you could use in PowerBuilder applications to update other columns
in the database. If their return values are compatible with PowerBuilder
datatypes, you can use CLR datatype methods in PowerScript, dynamic
SQL, embedded SQL or in DataWindow objects, because the SQL script
is executed on the SQL Server side. </p>
<A NAME="TI1272"></A><p>The CLR datatypes can also be mapped to Strings in PowerScript,
but the retrieved data is a hexadecimal string representation of
binary data. </p>
<A NAME="TI1273"></A><p>You can use the above methods to work with all datatypes that
are implemented as CLR datatypes, such as the <A HREF="connpbp61.htm#BABFIDJH">HierarchyID datatype</A>, <A HREF="connpbp61.htm#BABHGDBJ">Spatial datatypes</A>, and <A HREF="connpbp61.htm#BABBAIHF">User-defined types</A>.</p>
<A NAME="BABFIDJH"></A><h4>HierarchyID datatype</h4>
<A NAME="TI1274"></A><p>HierarchyID is a variable length, system datatype that can
store values representing nodes in a hierarchical tree, such as
an organizational structure. A value of this datatype represents
a position in the tree hierarchy.</p>
<p><b>    ISQL Usage</b>   You can use HierarchyID columns with CREATE TABLE, SELECT,
UPDATE, INSERT, and DELETE statements in the ISQL painter. For example:<p><PRE> CREATE TABLE Emp ( </PRE><PRE>   EmpId int NOT NULL, </PRE><PRE>   EmpName varchar(20) NOT NULL, </PRE><PRE>   EmpNode hierarchyid NULL);</PRE></p>
<A NAME="TI1275"></A><p>To insert HierarchyID data, you can use the canonical string
representation of HierarchyID or any of the methods associated with
the HierarchyID datatype as shown below.<p><PRE> INSERT into Emp VALUES (1, 'Scott', </PRE><PRE>   hierarchyid::GetRoot());</PRE><PRE> INSERT into Emp VALUES (2, 'Tom' , '/1/');</PRE><PRE> </PRE><PRE> DECLARE @Manager hierarchyid </PRE><PRE> SELECT @Manager = hierarchyid::GetRoot() FROM Emp INSERT into Emp VALUES (2, 'Tom',     </PRE><PRE>   @Manager.GetDescendant(NULL,NULL));</PRE><PRE> DECLARE @Employee hierarchyid </PRE><PRE> SELECT @Employee = CAST('/1/2/3/4/' AS hierarchyid) </PRE><PRE> INSERT into Emp VALUES (2, 'Jim' , @Employee);</PRE></p>
<A NAME="TI1276"></A><p>You cannot select the HierarchyID column directly since it
has binary data, and the ISQL painter Results view does not display
binary columns. However, you can retrieve the HierarchyID data as
a string value using the <b>ToString</b> method of HierarchyID.
For example:<p><PRE> Select EmpId, EmpName, EmpNode.ToString() from Emp;</PRE></p>
<A NAME="TI1277"></A><p>You can also use the following methods on HierarchyID columns
to retrieve its data: <b>GetAncestor</b>, <b>GetDescendant</b>, <b>GetLevel</b>, <b>GetRoot</b>, <b>IsDescendant</b>, <b>Parse</b>, and <b>Reparent</b>.
If one of these methods returns a HierarchyID node, then use <b>ToString</b> to
convert the data to a string. For example:<p><PRE> Select EmpId, EmpName, EmpNode.GetLevel() from Emp;</PRE><PRE> Select EmpId, EmpName,  </PRE><PRE>    EmpNode.GetAncestor(1).ToString() from Emp;</PRE></p>
<A NAME="TI1278"></A><p>HierarchyID columns can be updated using a String value or
a HierarchyID variable:        <p><PRE> Update Emp Set EmpNode = '/1/2/' where EmpId=4;</PRE><PRE> Delete from Emp where EmpNode = '/1/2/';</PRE></p>
<p><b>PowerScript Usage</b>   You can use HierarchyID columns in embedded SQL statements
for SELECT, INSERT, UPDATE, and DELETE operations. HierarchyID data
can be retrieved either as a String or as a Binary(Blob) datatype
using the SelectBlob statement.</p>
<A NAME="TI1279"></A><p>When using a String datatype to retrieve HierarchyID data,
use the <b>ToString</b> method. Otherwise the data will
be a hexadecimal  representation of the binary HierarchyID value.</p>
<A NAME="TI1280"></A><p>The following example shows how you can use HierarchyID methods
in embedded SQL:<p><PRE> long id</PRE><PRE> String hid,nameSelect EmpId, EmpName, EmpNode.ToString() </PRE><PRE>   into :id, :name, :hid   </PRE><PRE>   from Emp where EmpId=3;</PRE><PRE> Select EmpId, EmpName, EmpNode.GetLevel() </PRE><PRE>   into :id, :name, :hid   </PRE><PRE>   from Emp where EmpId=3;</PRE><PRE> </PRE><PRE> Blob b</PRE><PRE> Selectblob EmpNode into :b from Emp where EmpId =2;</PRE></p>
<p><b>    DataWindow Usage</b>   DataWindow objects do not directly support the HierarchyID
datatype. But you can convert the HierarchyID to a string using the <b>ToString</b> method
or an associated HierarchyID method in the data source SQL. For
example:<p><PRE> SELECT EmpId, EmpName, EmpNode.ToString() FROM Emp; </PRE><PRE> SELECT EmpId, EmpName, EmpNode.GetLevel() FROM Emp;</PRE></p>
<A NAME="BABHGDBJ"></A><h4>Spatial datatypes</h4>
<A NAME="TI1281"></A><p>Microsoft SQL Server 2008 supports two spatial datatypes:
the geometry datatype and the geography datatype. In SQL Server,
these datatypes are implemented as .NET Common Language Runtime
(CLR) datatypes.</p>
<A NAME="TI1282"></A><p>Although the PowerBuilder SNC interface does not work with
CLR datatypes, you can convert the spatial datatypes into strings
(with the <b>ToString</b> function) and use them in
PowerScript, in the ISQL painter, in embedded SQL, and in DataWindow
objects. This is similar to the way you use the HierarchyID datatype.
The SelectBlob SQL statement also lets you retrieve binary values for
these datatypes.</p>
<A NAME="TI1283"></A><p>The geography and geometry datatypes support eleven different
data objects, or instance types, but only seven of these types are
instantiable: Points, LineStrings, Polygons, and the objects in
an instantiable GeometryCollection (MultiPoints, MultiLineStrings,
and MultiPolygons). You can create and work with these objects in
a database, calling methods associated with them, such as <b>STAsText</b>, <b>STArea</b>, <b>STGeometryType</b>,
and so on. For example:<p><PRE> CREATE TABLE SpatialTable (id int IDENTITY (1,1),      </PRE><PRE>   GeomCol geometry);</PRE><PRE> INSERT INTO SpatialTable (GeomCol) VALUES (</PRE><PRE>   geometry::STGeomFromText(</PRE><PRE>    'LINESTRING (100 100,20 180,180 180)',0));</PRE><PRE> select id, GeomCol.ToString() from SpatialTable;</PRE><PRE> select id, GeomCol.STAsText(), </PRE><PRE>   GeomCol.STGeometryType(),</PRE><PRE>   GeomCol.STArea() from SpatialTable;</PRE></p>
<A NAME="BABBAIHF"></A><h4>User-defined types</h4>
<A NAME="TI1284"></A><p>User-defined types (UDTs) are implemented in SQL Server as
CLR types and integrated with .NET. Microsoft SQL Server 2008 eliminates
the 8 KB limit for UDTs, enabling the size of UDT data to expand
dramatically. Although the PowerBuilder SNC interface does not directly
support UDT datatypes, you can use the <b>ToString</b> method
to retrieve data for UDTs in the same way as for other CLR datatypes
such as HierarchyId or the spatial datatypes. However, if a UDT
datatype is mapped to a String datatype in PowerScript, UDT binary
values will be retrieved as hexadecimal strings. To retrieve or
update data in binary form (blob) from a UDT, you can use the SelectBlob
or UpdateBlob SQL statements, respectively.</p>
<A NAME="TI1285"></A><p>You can use any of the associated methods of UDT or CLR datatypes
that return compatible data (such as String, Long, Decimal, and
so on) for PowerBuilder applications. </p>
<A NAME="TI1286"></A><h2>T-SQL enhancements</h2>
<A NAME="TI1287"></A><h4>MERGE statement</h4>
<A NAME="TI1288"></A><p>The MERGE Transact-SQL statement performs INSERT, UPDATE,
or DELETE operations on a target table or view based on the results
of a join with a source table.     You can use MERGE statement
in the ISQL painter and in PowerScript using dynamic SQL. For example<p><PRE> String mySQL</PRE><PRE> mySQL = "MERGE INTO a USING b ON a.keycol = b.keycol " &amp;</PRE><PRE>   + "WHEN MATCHED THEN "&amp;</PRE><PRE>   + "UPDATE SET col1 = b.col1,col2 = b.col2 " &amp;</PRE><PRE>   + "WHEN NOT MATCHED THEN " &amp;</PRE><PRE>   + "INSERT (keycol, col1, col2, col3)" &amp; </PRE><PRE>   + "VALUES (b.keycol, b.col1, b.col2, b.col3) " &amp;</PRE><PRE>   + "WHEN SOURCE NOT MATCHED THEN " &amp;</PRE><PRE>   + "DELETE;"</PRE><PRE> EXECUTE IMMEDIATE :Mysql;</PRE></p>
<p><img src="images/note.gif" width=17 height=17 border=0 align="bottom" alt="Note"> <span class=shaded>Using the MERGE statement in ISQL</span> <A NAME="TI1289"></A>A MERGE statement must be terminated by a semicolon. By default
the ISQL painter uses a semicolon as a SQL terminating character,
so to use a MERGE statement in ISQL, the terminating character must
be changed to a colon (:), a forward slash (/), or some
other special character.</p>
<A NAME="TI1290"></A><h4>Grouping sets</h4>
<A NAME="TI1291"></A><p>GROUPING SETS is an extension of the GROUP BY clause that
lets you define multiple groupings in the same query. GROUPING SETS
produce a single result set, making aggregate querying and reporting
easier and faster. It is equivalent to a UNION ALL operation for
differently grouped rows.The GROUPING SETS, ROLLUP, and CUBE operators
are added to the GROUP BY clause. A new function, GROUPING_ID,
returns more grouping-level information than the existing
GROUPING function. (The WITH ROLLUP, WITH CUBE, and ALL syntax is
not ISO compliant and is therefore deprecated.)The following example
uses the GROUPING SETS operator and the GROUPING_ID function:<p><PRE> SELECT EmpId, Month, Yr, SUM(Sales) AS Sales</PRE><PRE>   FROM Sales </PRE><PRE>   GROUP BY GROUPING SETS((EmpId, ROLLUP(Yr, Month)));</PRE><PRE> SELECT COL1, COL2, </PRE><PRE>   SUM(COL3) AS TOTAL_VAL, </PRE><PRE>   GROUPING(COL1) AS C1, </PRE><PRE>   GROUPING(COL2) AS C2, </PRE><PRE>   GROUPING_ID(COL1, COL2) AS GRP_ID_VALUE</PRE><PRE>   FROM TEST_TBL GROUP BY ROLLUP (COL1, COL2);</PRE></p>
<A NAME="TI1292"></A><p>You can use the GROUPING SETS operator in the ISQL painter,
in PowerScript (embedded SQL and dynamic SQL) and in DataWindow
objects (syntax mode).</p>
<A NAME="TI1293"></A><h4>Row constructors</h4>
<A NAME="TI1294"></A><p>Transact-SQL now allows multiple value inserts within a single
INSERT statement. You can use the enhanced INSERT statement in the
ISQL painter and in PowerScript (embedded SQL and dynamic SQL).
For example:<p><PRE> INSERT INTO Employees VALUES ('tom', 25, 5), </PRE><PRE>   ('jerry', 30, 6), ('bok', 25, 3);</PRE></p>
<A NAME="TI1295"></A><p>When including multiple values in a single INSERT statement
with host variables, you must set the DisableBind DBParm to 1. If
you use literal values as in the above example, you can insert multiple
rows in a single INSERT statement regardless of the binding setting.</p>
<A NAME="TI1296"></A><h4>Compatibility level</h4>
<A NAME="TI1297"></A><p>In SQL Server 2008, the ALTER DATABASE statement allows you
to set the database compatibility level (SQL Server version), replacing
the sp_dbcmptlevel procedure. You can use this syntax in
the ISQL painter and in PowerScript (dynamic SQL). For example:<p><PRE> ALTER DATABASE &lt;database_name&gt; </PRE><PRE>   SET COMPATIBILITY_LEVEL = {80 | 90 | 100}</PRE><PRE> 80 = SQL Server 2000 </PRE><PRE> 90 = SQL Server 2005     </PRE><PRE> 100 = SQL Server 2008</PRE></p>
<A NAME="TI1298"></A><p>Compatibility level affects behaviors for the specified database
only, not for the entire database server. It provides only partial
backward compatibility with earlier versions of SQL Server. You
can use the database compatibility level as an interim migration
aid to work around differences in the behaviors of different versions
of the database.</p>
<A NAME="TI1299"></A><h4>Table hints</h4>
<A NAME="TI1300"></A><p>The FORCESEEK table hint overrides the default behavior of
the query optimizer. It provides advanced performance tuning options,
instructing the query optimizer to use an index seek operation as
the only access path to the data in the table or view that is referenced
by the query. You can use the FORCESEEK table hint in the ISQL painter,
in PowerScript (embedded SQL and dynamic SQL), and in DataWindow
objects (syntax mode). For example:<p><PRE> Select ProductID, OrderQty from SalesOrderDetail </PRE><PRE>    with (FORCESEEK);</PRE></p>
<A NAME="TI1301"></A><h2>Unsupported SQL Server 2008 features</h2>
<A NAME="TI1302"></A><p>The PowerBuilder SNC interface does not support the     User-Defined
Table Type (a user-defined type that represents the definition of
a table structure) that was introduced in SQL Server 2008. </p>

